package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.SMS;

public class SMSMod {
	Connection connection;
	public static SMSMod isntanceSms;

	public static SMSMod getIsntance() {
		if (isntanceSms == null) {
			isntanceSms = new SMSMod();
		}
		return isntanceSms;
	}
	public boolean isSMSid(int smsid){
		boolean result = false;
		connection = DB_Connection.getIsntance().getConnection();
		try {
			String sql = "SELECT * FROM sms WHERE smsid = ? ";
			PreparedStatement sqli = connection.prepareStatement(sql);
			sqli.setInt(1, smsid);
			int n = sqli.executeUpdate();
			if(n > 0) result = true ;
			sqli.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean add(SMS sms)  {
		boolean result = false;
		connection = DB_Connection.getIsntance().getConnection();
		PreparedStatement sqli = null;
		String sql = "INSERT INTO sms(uid , contents , date) VALUES (? , ? , NOW())";
		try {
			sqli = connection.prepareStatement(sql);
			sqli.setInt(1, sms.getUid());
			sqli.setString(2, sms.getContents());
			int n = sqli.executeUpdate();
			if (n > 0)
				result = true;
			sqli.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return result;
	}
	public SMS getSMS(int smsid){
		SMS result = null;
		connection = DB_Connection.getIsntance().getConnection();
		try {
			String sql = "SELECT * FROM sms WHERE smsid = ? ";
			PreparedStatement sqli = connection.prepareStatement(sql);
			sqli.setInt(1, smsid);
			ResultSet rs = sqli.executeQuery();
			while(rs.next()){
				result = new SMS(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getDate(4));
			}
			sqli.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public SMS getSMS(int uid,String ct){
		SMS result = null;
		connection = DB_Connection.getIsntance().getConnection();
		try {
			String sql = "SELECT * FROM sms WHERE uid = ? AND contents = ? ";
			PreparedStatement sqli = connection.prepareStatement(sql);
			sqli.setInt(1, uid);
			sqli.setString(2, ct);
			ResultSet rs = sqli.executeQuery();
			while(rs.next()){
				result = new SMS(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getDate(4));
			}
			sqli.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public SMS getSMS(SMS sms){
		SMS result = null;
		connection = DB_Connection.getIsntance().getConnection();
		try {
			String sql = "SELECT * FROM sms WHERE uid = ? AND contents = ? ";
			PreparedStatement sqli = connection.prepareStatement(sql);
			sqli.setInt(1, sms.getUid());
			sqli.setString(2,sms.getContents());
			ResultSet rs = sqli.executeQuery();
			while(rs.next()){
				result = new SMS(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getDate(4));
			}
			sqli.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public ArrayList<SMS> getSMSByUid(int uid){
		ArrayList<SMS> result = null;
		connection = DB_Connection.getIsntance().getConnection();
		try {
			String sql = "SELECT * FROM sms WHERE uid = ?";
			PreparedStatement sqli = connection.prepareStatement(sql);
			sqli.setInt(1, uid);
			ResultSet rs = sqli.executeQuery();
			while(rs.next()){
				result.add(new SMS(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getDate(4)));
			}
			sqli.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public ArrayList<SMS> getALL(){
		ArrayList<SMS> result = null;
		connection = DB_Connection.getIsntance().getConnection();
		try {
			String sql = "SELECT * FROM sms";
			PreparedStatement sqli = connection.prepareStatement(sql);
			ResultSet rs = sqli.executeQuery();
			while(rs.next()){
				result.add(new SMS(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getDate(4)));
			}
			sqli.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean del(int id){
				PreparedStatement delpd = null;
				boolean result = false;
				Connection connection = DB_Connection.getIsntance().getConnection();
				try {
					delpd = connection.prepareStatement("DELETE FROM sms WHERE smsid = ? LIMIT 1");
					delpd.setInt(1, id);
					int number = delpd.executeUpdate();
					if (number > 0) {
						result = true;
					}
					delpd.close();
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return  result;
			}
	public boolean delByUid(int id){
		PreparedStatement delpd = null;
		boolean result = false;
		Connection connection = DB_Connection.getIsntance().getConnection();
		try {
			delpd = connection.prepareStatement("DELETE FROM sms WHERE uid = ? LIMIT 1");
			delpd.setInt(1, id);
			int number = delpd.executeUpdate();
			if (number > 0) {
				result = true;
			}
			delpd.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return  result;
	}
}
